package com.teamall.web.sjtj.db.dao.mapper;

import com.teamall.web.sjtj.vo.*;
import com.teamall.web.sys.db.po.SysUserPO;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * ${DESCRIPTION}
 *
 * @author
 * @create 2017-09-08 0:50
 **/
public interface ISjtjJzsbMapper {
    @Select("select TO_DICT(poverty_attr,200) povertyAttrName, count(1) familyCnt from STAT_FAMILY_POVERTY where stat_date=#{date} group by poverty_attr")
    List<PkfbChatVO> getPkfbMain(String date);
    @Select("select town_name townName,\n" +
            "\tMAX(CASE povertyAttrName WHEN '一般贫困户' THEN familyCnt ELSE 0 END ) ybpk,\n" +
            "  MAX(CASE povertyAttrName WHEN '低保贫困户' THEN familyCnt ELSE 0 END ) dbpk,\n" +
            "  MAX(CASE povertyAttrName WHEN '五保贫困户' THEN familyCnt ELSE 0 END ) wbpk,\n" +
            "\tMAX(CASE povertyAttrName WHEN '一般农户' THEN familyCnt ELSE 0 END ) ybnf\n" +
            " from (\n" +
            " select town_name,TO_DICT(poverty_attr,200) povertyAttrName, count(1) familyCnt from STAT_FAMILY_POVERTY \n" +
            "where stat_date=#{date} group by poverty_attr ,town_name) test_tb_grade GROUP BY town_name")
    List<PkfbTableVO> getPkfbTableData(String date);

    @Select("SELECT * from sys_user limit 0,10")
    List<SysUserPO> getSysUser(String date);
    @Select("select  TO_DICT(main_reason,201)  mainReasonName, count(1) familyCnt from STAT_FAMILY_POVERTY where stat_date=#{date} group by main_reason")
    List<ZpyyChatVO> getZpyyMain(String date);
    @Select("\n" +
            "select townName,\n" +
            "\tMAX(CASE mainReasonName WHEN '因灾' THEN familyCnt ELSE 0 END ) yz,\n" +
            "\tMAX(CASE mainReasonName WHEN '缺劳力' THEN familyCnt ELSE 0 END ) qll,\n" +
            "\tMAX(CASE mainReasonName WHEN '因病' THEN familyCnt ELSE 0 END ) yb,\n" +
            "  MAX(CASE mainReasonName WHEN '自身发展动力不' THEN familyCnt ELSE 0 END ) zsfa,\n" +
            "\tMAX(CASE mainReasonName WHEN '缺水' THEN familyCnt ELSE 0 END ) qs,\n" +
            "\tMAX(CASE mainReasonName WHEN '因残' THEN familyCnt ELSE 0 END ) yc,\n" +
            "  MAX(CASE mainReasonName WHEN '缺学' THEN familyCnt ELSE 0 END ) qll,\n" +
            "\tMAX(CASE mainReasonName WHEN '缺土地' THEN familyCnt ELSE 0 END ) qtd,\n" +
            "\tMAX(CASE mainReasonName WHEN '缺资金' THEN familyCnt ELSE 0 END ) qzj,\n" +
            "\tMAX(CASE mainReasonName WHEN '缺技术' THEN familyCnt ELSE 0 END ) qjs\n" +
            " from \n" +
            "(select town_name townName,  TO_DICT(main_reason,201)  mainReasonName, count(1) familyCnt from STAT_FAMILY_POVERTY where stat_date=#{date} group by main_reason,townName\n" +
            ")\n" +
            "tb GROUP BY townName")
    List<ZpyyTableVO> getZpyyTableData(String date);
    @Select("select a.town_id,a.town_name townName,ifnull(sum(total_cnt),0) totalCnt,ifnull(sum(l1_cnt),0) l1cnt,ifnull(sum(l2_cnt),0) l2cnt, \n" +
            "ifnull(sum(l3_cnt),0) l3cnt,ifnull(sum(l4_cnt),0) l4cnt,ifnull(sum(l5_cnt),0) l5cnt,ifnull(sum(l6_cnt),0) l6cnt,ifnull(sum(l7_cnt),0) l7cnt,ifnull(sum(all_cnt),0) allcnt  from TOWN_INFO a\n" +
            "left outer join V_STAT_VILLAGE_INCOME b on a.town_id=b.town_id and stat_date=#{date}\n" +
            "group by a.town_id,a.town_name")
    List<PksrTableVO> getPksrTableData(String date);
}